using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

using Entities;
using IDAL;

namespace SQLDBDAL
{
    public class OrderDAO : IOrderDAO
    {
        private const string SQL_SELECT_ORDER = "SELECT * FROM Orders";

        private const string SQL_SELECT_ORDER_BY_ID = "SELECT * FROM Orders WHERE OrderID = @OrderID";

        private const string SQL_SELECT_ORDER_BY_USERNAME = "SELECT * FROM Orders WHERE Username=@Username";

        private const string SQL_SELECT_ORDER_BY_ORDERDATE = "SELECT * FROM Orders WHERE OrderDate = (SELECT CAST(FLOOR(CAST( getdate() AS float)) AS datetime))";

        private const string SQL_SELECT_TOTAL_BY_ORDERDATE = "SELECT SUM(SubTotal) FROM Orders WHERE OrderDate = (SELECT CAST(FLOOR(CAST( getdate() AS float)) AS datetime))";

        private const string SQL_SELECT_ORDER_BETWEEN_DAY = "SELECT * FROM Orders WHERE OrderDate BETWEEN @Dayfrom AND @Dayto";

        private const string SQL_SELECT_TOTAL_BETWEEN_DAY = "SELECT SUM(SubTotal) FROM Orders WHERE OrderDate BETWEEN @Dayfrom AND @Dayto";

        //private const string SQL_INSERT_ORDER = "INSERT INTO Orders (Username, ShipID, OrderDate, RequiredDate, ShipName, ShipAddress, ShipCity) " +
        //                                            "VALUES (@Username, @ShipID, GETDATE(), DATEADD(d,3, GETDATE()), @ShipName, @ShipAddress, @ShipCity)";

        private const string SQL_INSERT_ORDER = "INSERT INTO Orders (Username, ShipID, OrderDate, RequiredDate, ShipName, ShipAddress, ShipCity, SubTotal) " +
                                            "VALUES (@Username, @ShipID, @OrderDate, @RequiredDate, @ShipName, @ShipAddress, @ShipCity, @SubTotal)";

        private const string SQL_UPDATE_SHIPPED_DATE = "UPDATE Orders SET ShippedDate=@ShippedDate WHERE OrderID=@OrderID";              

        // Parameters
        private const string PARM_ORDER_ID = "@OrderID";
        private const string PARM_USER_NAME = "@Username";
        private const string PARM_SHIP_ID = "@ShipID";
        private const string PARM_ORDER_DATE = "@OrderDate";
        private const string PARM_REQUIRED_DATE = "@RequiredDate";
        private const string PARM_SHIPPED_DATE = "@ShippedDate";
        private const string PARM_SHIP_NAME = "@ShipName";
        private const string PARM_SHIP_ADDRESS = "@ShipAddress";
        private const string PARM_SHIP_CITY = "@ShipCity";
        private const string PARM_SUBTOTAL = "@SubTotal";
        private const string PARM_DAYFROM = "@Dayfrom";
        private const string PARM_DAYTO = "@Dayto";

        #region IOrderDAO Members

        public IList<OrderInfo> GetAll()
        {
            IList<OrderInfo> orders = new List<OrderInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_ORDER, null))
            {
                while (reader.Read())
                {
                    OrderInfo order = new OrderInfo();
                    order.OrderID = reader.GetInt32(0);
                    order.UserName = reader.GetString(1);
                    order.ShipID = reader.GetInt32(2);
                    order.OrderDate = reader.GetDateTime(3);
                    order.RequireDate = reader.GetDateTime(4);
                    if(!reader.IsDBNull(5))
                        order.ShippedDate = reader.GetDateTime(5);
                    order.ShipName = reader.GetString(6);
                    order.ShipAddress = reader.GetString(7);
                    order.ShipCity = reader.GetString(8);
                    order.SubTotal = reader.GetDecimal(9);
                    orders.Add(order);
                }
            }
            return orders;            
        }

        public OrderInfo GetByOrderID(string orderID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_ORDER_ID, SqlDbType.Int) };
            if(!string.IsNullOrEmpty(orderID))
                parms[0].Value = int.Parse(orderID);

            OrderInfo order = new OrderInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_ORDER_BY_ID, parms))
            {
                while (reader.Read())
                {
                    order.OrderID = reader.GetInt32(0);
                    order.UserName = reader.GetString(1);
                    order.ShipID = reader.GetInt32(2);
                    order.OrderDate = reader.GetDateTime(3);
                    order.RequireDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        order.ShippedDate = reader.GetDateTime(5);
                    order.ShipName = reader.GetString(6);
                    order.ShipAddress = reader.GetString(7);
                    order.ShipCity = reader.GetString(8);
                    order.SubTotal = reader.GetDecimal(9);
                    order.ShipMethod = new ShipDAO().GetByID(order.ShipID.ToString()).ShipVia;
                    order.ShipPrice = new ShipDAO().GetByID(order.ShipID.ToString()).ShipPrice;
                    order.OrderDetail = new OrderDetailDAO().GetByID(orderID);
                }
            }
            return order;
        }

        public IList<OrderInfo> GetByUserName(string username)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_USER_NAME, SqlDbType.NVarChar) };
            parms[0].Value = username;

            IList<OrderInfo> orders = new List<OrderInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ORDER_BY_USERNAME, parms))
            {
                while (reader.Read())
                {
                    OrderInfo order = new OrderInfo();
                    order.OrderID = reader.GetInt32(0);
                    order.UserName = reader.GetString(1);
                    order.ShipID = reader.GetInt32(2);
                    order.OrderDate = reader.GetDateTime(3);
                    order.RequireDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        order.ShippedDate = reader.GetDateTime(5);
                    order.ShipName = reader.GetString(6);
                    order.ShipAddress = reader.GetString(7);
                    order.ShipCity = reader.GetString(8);
                    order.SubTotal = reader.GetDecimal(9);
                    order.ShipMethod = new ShipDAO().GetByID(order.ShipID.ToString()).ShipVia;
                    order.ShipPrice = new ShipDAO().GetByID(order.ShipID.ToString()).ShipPrice;
                    order.OrderDetail = new OrderDetailDAO().GetByID(order.OrderID.ToString());
                    orders.Add(order);
                }                
            }
            return orders;
        }
        public IList<OrderInfo> GetByOrderDate()
        {

            IList<OrderInfo> orders = new List<OrderInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ORDER_BY_ORDERDATE, null))
            {
                while (reader.Read())
                {
                    OrderInfo order = new OrderInfo();
                    order.OrderID = reader.GetInt32(0);
                    order.UserName = reader.GetString(1);
                    order.ShipID = reader.GetInt32(2);
                    order.OrderDate = reader.GetDateTime(3);
                    order.RequireDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        order.ShippedDate = reader.GetDateTime(5);
                    order.ShipName = reader.GetString(6);
                    order.ShipAddress = reader.GetString(7);
                    order.ShipCity = reader.GetString(8);
                    order.SubTotal = reader.GetDecimal(9);
                    orders.Add(order);
                }
            }
            return orders;
        }
        public string GetTotalOrderByOrderDay()
        {
            string result = "";           
            try
            {
                decimal i = (decimal)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_TOTAL_BY_ORDERDATE, null);
                result = i.ToString();
            }
            catch (Exception ex)
            {
                string s = ex.StackTrace;
                return null;
            }
            return result;
        }
        public IList<OrderInfo> GetOrderBetweenDay(string from, string to)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_DAYFROM, SqlDbType.DateTime),
                                         new SqlParameter(PARM_DAYTO, SqlDbType.DateTime)};
            parms[0].Value = DateTime.Parse(from);
            parms[1].Value = DateTime.Parse(to);

            IList<OrderInfo> orders = new List<OrderInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ORDER_BETWEEN_DAY, parms))
            {
                while (reader.Read())
                {
                    OrderInfo order = new OrderInfo();
                    order.OrderID = reader.GetInt32(0);
                    order.UserName = reader.GetString(1);
                    order.ShipID = reader.GetInt32(2);
                    order.OrderDate = reader.GetDateTime(3);
                    order.RequireDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        order.ShippedDate = reader.GetDateTime(5);
                    order.ShipName = reader.GetString(6);
                    order.ShipAddress = reader.GetString(7);
                    order.ShipCity = reader.GetString(8);
                    order.SubTotal = reader.GetDecimal(9);
                    orders.Add(order);
                }
            }
            return orders;
        }

        public string GetTotalOrderBetWeenDay(string from, string to)
        {
            string result = "";
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_DAYFROM, SqlDbType.DateTime),
                                         new SqlParameter(PARM_DAYTO, SqlDbType.DateTime)};
            parms[0].Value = DateTime.Parse(from);
            parms[1].Value = DateTime.Parse(to);

            try
            {
                decimal i = (decimal)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_TOTAL_BETWEEN_DAY, parms);
                result = i.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public int Insert(OrderInfo order)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
								        new SqlParameter(PARM_USER_NAME, SqlDbType.NVarChar),
								        new SqlParameter(PARM_SHIP_ID, SqlDbType.Int),
                                        new SqlParameter(PARM_ORDER_DATE, SqlDbType.DateTime),
                                        new SqlParameter(PARM_REQUIRED_DATE, SqlDbType.DateTime),
                                        //new SqlParameter(PARM_SHIPPED_DATE, SqlDbType.DateTime),
                                        new SqlParameter(PARM_SHIP_NAME, SqlDbType.NVarChar),
                                        new SqlParameter(PARM_SHIP_ADDRESS, SqlDbType.NVarChar),
                                        new SqlParameter(PARM_SHIP_CITY, SqlDbType.NVarChar), 
                                        new SqlParameter(PARM_SUBTOTAL, SqlDbType.Decimal) };

            parms[0].Value = order.UserName;
            parms[1].Value = order.ShipID;
            parms[2].Value = DateTime.Today;
            parms[3].Value = DateTime.Today.AddDays(3);
            //parms[2].Value = order.ShippedDate;
            parms[4].Value = order.ShipName;
            parms[5].Value = order.ShipAddress;
            parms[6].Value = order.ShipCity;
            parms[7].Value = order.SubTotal;

            try
            {
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_ORDER, parms);
                int id = SQLDBHelper.SetNewIdentity2(conn, order.OrderID);
                return id;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdateShippedDate(string orderID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_ORDER_ID, SqlDbType.Int) };
            if(!string.IsNullOrEmpty(orderID))
                parms[0].Value = int.Parse(orderID);

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_SHIPPED_DATE, parms);                
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
